<%@page import="org.postgresql.Driver"%>
<html>
<body>
	<%@ page language="java" import="java.sql.*"%>
	<%
		try {
			Class.forName("org.postgresql.Driver");
			Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/cse132b","cse132blogin", "1234567890");
			String action = request.getParameter("action");
			// Check if an insertion is requested
			if (action != null && action.equals("insert")) {
				// Create the prepared statement and use it to
				// INSERT the student attributes INTO the Student table.
				int NewID = 0;
				
				conn.setAutoCommit(false);
				
				PreparedStatement pstmt;
				if (request.getParameter("middlename").equals("")) {
					pstmt = conn
							.prepareStatement("INSERT INTO students (first_name,last_name,ssn,state_resident) VALUES (?,?,?,?)", Statement.RETURN_GENERATED_KEYS);
				} else {
					pstmt = conn
							.prepareStatement("INSERT INTO students (first_name,last_name,ssn,state_resident,middle_name) VALUES (?,?,?,?,?)", Statement.RETURN_GENERATED_KEYS);
				}
				pstmt.setString(1, request.getParameter("firstname"));
				pstmt.setString(2, request.getParameter("lastname"));
				pstmt.setString(3, request.getParameter("ssn"));
				pstmt.setString(4, request.getParameter("stateresident"));
				if (!request.getParameter("middlename").equals("")) {
					pstmt.setString(5, request.getParameter("middlename"));
				}
				int rowCount = pstmt.executeUpdate();
				ResultSet rs = pstmt.getGeneratedKeys();
				if (rs.next()) {
					NewID = rs.getInt(1);
				}
				rs.close();
				pstmt.close();
				
				String s="";
				if (request.getParameter("typeStudent").equals("MS")){
					s = "INSERT INTO MS (student_id) VALUES (?)";
				} 
				else if (request.getParameter("typeStudent").equals("Pre")){
					s = "INSERT INTO precandidate (student_id) VALUES (?)";
				}
				else if (request.getParameter("typeStudent").equals("Phd")){
					s = "INSERT INTO Phd (student_id) VALUES (?)";
				}
				
				if (!s.equals(""))
				{
					PreparedStatement pstmt1;
					pstmt1 = conn.prepareStatement(s);
					pstmt1.setInt(1, NewID);
					rowCount = pstmt1.executeUpdate();	
					pstmt1.close();
				}
					
				conn.commit();
				conn.setAutoCommit(true);
				// Commit transaction
				out.println("Successfully made new student.<br/>");
			}
	%>
	
	<table border="1">
		<tr>
			<td valign="top" width="40%">
				<%-- -------- Include menu HTML code -------- --%> <jsp:include
					page="/menu.html" />
			</td>
			<td>	
			
	<table border="0">
		<tr>
			<td valign="top">Students Entry Page</td>
		</tr>
		<tr>
			<table border="1">
				<tr>
					<th>First Name*</th>
					<th>Middle Name</th>
					<th>Last Name*</th>
					<th>SSN*</th>
					<th>State Resident*</th>
					<th>Type*</th>
				</tr>
				<tr>
					<form action="students.jsp" method="get">
						<input type="hidden" value="insert" name="action">
						<td><input value="" name="firstname"></td>
						<td><input value="" name="middlename"></td>
						<td><input value="" name="lastname"></td>
						<td><input value="" name="ssn"></td>
						<td>
							<select name="stateresident">
									<option value="c">California Resident</option>
									<option value="f">Foreign Student</option>
									<option value="n">Non-CA US Resident</option>
							</select>	
						</td>
						<td>
							<select name="typeStudent">
									<option value="Under">Under Graduate</option>
									<option value="MS">Master</option>
									<option value="Pre">Phd Pre-Candidate</option>
									<option value="Phd">Phd Candidate</option>
							</select>	
						</td>
						<td><input type="submit"></input></td>
					</form>
				</tr>
			</table>
		</tr>
	</table>
	<%
		} catch (SQLException ex) {
			out.print("SQLException: " + ex.getMessage() + "<br/>");
			out.print("SQLState: " + ex.getSQLState() + "<br/>");
			out.print("VendorError: " + ex.getErrorCode() + "<br/>");
		}
	%>
	<%
		
	%>
</td>
</tr>
</table>
</body>
</html>